package com.lanyou.esb.service;

import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Service;

import com.lanyou.cook.entity.domain.userManagement.User;
import com.lanyou.cook.service.security.UserSessionService;
import com.lanyou.esb.web.param.SocketLogParam;

@Service
public class SocketLogService {

	@PersistenceContext
	private EntityManager entityManager;
	
	@Autowired
	private UserSessionService userSessionService;
	
	public List<Object> countAllSocketInterfaceLogs(SocketLogParam param, Pageable pageable){
		String userId = userSessionService.getCurrentAuthenticatedUser().getId();
		//创建select语句
		StringBuilder builder = new StringBuilder(
				"SELECT COUNT(V1.IF_SOCKET_ID) FROM (" + " SELECT  tlg.IF_SOCKET_ID, tlg.IF_NAME,"
						+ " tlg.TRANSPORT_DATE, tlg.TRANSPORT_RESULT" + " FROM test_socket_log tlg "
						+ " where EXISTS(SELECT tus.FIELD_SYSTEM_ID FROM t_cook_bu_user_system tus WHERE tlg.FIELD_SYSTEM_ID = tus.FIELD_SYSTEM_ID AND tus.USER_ID = '" + userId + "')");
		
		
		//创建内层的where语句
		if(StringUtils.isNotBlank(param.getCreateTime()) && StringUtils.isBlank(param.getEndTime())){
			String whereSql = "and tlg.TRANSPORT_DATE > '" + param.getCreateTime() + "') AS V1";
			builder.append(whereSql);
		}
		
		if(StringUtils.isBlank(param.getCreateTime()) && StringUtils.isNotBlank(param.getEndTime())){
			String whereSql = "and tlg.TRANSPORT_DATE < '" + param.getEndTime() + "') AS V1";
			builder.append(whereSql);
		}
		
		if(StringUtils.isNotBlank(param.getCreateTime()) && StringUtils.isNotBlank(param.getEndTime())){
			String whereSql = "and tlg.TRANSPORT_DATE > '" + param.getCreateTime() + "' and tlg.TRANSPORT_DATE < '" + param.getEndTime() + "') AS V1";
			builder.append(whereSql);
		}
		
		if(StringUtils.isBlank(param.getCreateTime()) && StringUtils.isBlank(param.getEndTime())){
			String whereSql = ") AS V1";
			builder.append(whereSql);
		}
		
		//创建外层的where语句
		if(StringUtils.isNotBlank(param.getTransportResult()) && StringUtils.isBlank(param.getInterfaceName())){
			String whereSql = " where V1.TRANSPORT_RESULT=" + param.getTransportResult();
			builder.append(whereSql);
		}
		
		if(StringUtils.isBlank(param.getTransportResult()) && StringUtils.isNotBlank(param.getInterfaceName())){
			String whereSql = " where V1.IF_NAME LIKE '" + param.getInterfaceName() + "%'";
			builder.append(whereSql);
		}
		
		if(StringUtils.isNotBlank(param.getTransportResult()) && StringUtils.isNotBlank(param.getInterfaceName())){
			String whereSql = " where V1.TRANSPORT_RESULT=" + param.getTransportResult() + " and V1.IF_NAME LIKE '" + param.getInterfaceName() + "%'";
			builder.append(whereSql);
		}
		
		
		Query query = entityManager.createNativeQuery(builder.toString());
		List<Object> result = query.getResultList();
		return result;
	}
	
	
	public List<Object[]> findAllSocketInterfaceLogs(SocketLogParam param, Pageable pageable){
		String userId = userSessionService.getCurrentAuthenticatedUser().getId();
		//创建select语句
		StringBuilder builder = new StringBuilder(
				"SELECT V0.FIELD_NAME, V0.SYSTEM_NAME, V0.IF_NAME, V0.SOURCE_ADDRESS, V0.TARGET_ADDRESS, V0.FILE_NAME, V0.FILE_SIZE, V0.EXECUTE_TASK_NODE, V0.TRANSPORT_DATE,"
					+ " V0.TRANSPORT_RESULT, V0.TRANSPORT_RESULT_DES" + " FROM" + " ("
					+ " SELECT (SELECT tf.FIELD_NAME FROM t_cook_db_field tf, t_cook_bu_field_system tfs where tf.FIELD_ID = tfs.FIELD_ID AND tfs.FIELD_SYSTEM_ID = tlg.FIELD_SYSTEM_ID) FIELD_NAME, "
					+ " (SELECT tfs.SYSTEM_NAME FROM t_cook_bu_field_system tfs where tfs.FIELD_SYSTEM_ID = tlg.FIELD_SYSTEM_ID) SYSTEM_NAME, tlg.IF_SOCKET_ID, tlg.SOURCE_ADDRESS, tlg.TARGET_ADDRESS, tlg.IF_NAME, tlg.FILE_NAME,"
					+ " tlg.FILE_SIZE, tlg.EXECUTE_TASK_NODE, tlg.TRANSPORT_DATE, tlg.TRANSPORT_RESULT, tlg.TRANSPORT_RESULT_DES"
					+ " FROM test_socket_log tlg "
					+ " where EXISTS(SELECT tus.FIELD_SYSTEM_ID FROM t_cook_bu_user_system tus WHERE tlg.FIELD_SYSTEM_ID = tus.FIELD_SYSTEM_ID AND tus.USER_ID = '" + userId + "')");
		
		
		boolean flag = false;
		//创建内层的where语句
		if(StringUtils.isNotBlank(param.getCreateTime()) && StringUtils.isBlank(param.getEndTime())){
			String whereSql = "and tlg.TRANSPORT_DATE > '" + param.getCreateTime() + "' ";
			builder.append(whereSql);
			flag = true;
		}
		
		if(StringUtils.isBlank(param.getCreateTime()) && StringUtils.isNotBlank(param.getEndTime())){
			String whereSql = "and tlg.TRANSPORT_DATE < '" + param.getEndTime() + "' ";
			builder.append(whereSql);
			flag = true;
		}
		
		if(StringUtils.isNotBlank(param.getCreateTime()) && StringUtils.isNotBlank(param.getEndTime())){
			String whereSql = "and tlg.TRANSPORT_DATE > '" + param.getCreateTime() + "' and tlg.TRANSPORT_DATE < '" + param.getEndTime() + "' ";
			builder.append(whereSql);
			flag = true;
		}
		
		/*if(StringUtils.isBlank(param.getCreateTime()) && StringUtils.isBlank(param.getEndTime())){
			String whereSql = ") AS V1";
			builder.append(whereSql);
		}*/
		
		
		//创建外层的where语句
		if(StringUtils.isNotBlank(param.getTransportResult()) && StringUtils.isBlank(param.getInterfaceName())){
			String whereSql = "";
			if(flag){
				whereSql = " and tlg.TRANSPORT_RESULT=" + param.getTransportResult();
			}else{
				whereSql = " and tlg.TRANSPORT_RESULT=" + param.getTransportResult();
			}
			builder.append(whereSql);
		}
		
		if(StringUtils.isBlank(param.getTransportResult()) && StringUtils.isNotBlank(param.getInterfaceName())){
			String whereSql = "";
			if(flag){
				whereSql = " and tlg.IF_NAME LIKE '" + param.getInterfaceName() + "%'";
			}else{
				whereSql = " and tlg.IF_NAME LIKE '" + param.getInterfaceName() + "%'";
			}
			builder.append(whereSql);
		}
		
		if(StringUtils.isNotBlank(param.getTransportResult()) && StringUtils.isNotBlank(param.getInterfaceName())){
			String whereSql = "";
			if(flag){
				whereSql = " and tlg.TRANSPORT_RESULT=" + param.getTransportResult() + " and tlg.IF_NAME LIKE '" + param.getInterfaceName() + "%'";
			}else{
				whereSql = " and tlg.TRANSPORT_RESULT=" + param.getTransportResult() + " and tlg.IF_NAME LIKE '" + param.getInterfaceName() + "%'";
			}
			builder.append(whereSql);
		}
		
		//分页
		String limitSql = ") AS V0 ORDER BY V0.TRANSPORT_DATE DESC LIMIT " + pageable.getPageNumber() * pageable.getPageSize() + "," + pageable.getPageSize();
		builder.append(limitSql);
		Query query = entityManager.createNativeQuery(builder.toString());
		List<Object[]> result = query.getResultList();
		
		return result;
	}
	
	
}
